﻿Imports System.Data.SqlClient

Public Class frmStaffleavePlan

    Sub GetNoOfDays()
        Dim DateOne As Date
        Dim DateTwo As Date
        Dim arrDate As New ArrayList
        Dim iNumOfDays As Long
        Dim iLoop As Integer

        DateOne = New Date(DtTo.Value.Year, DtTo.Value.Month, DtTo.Value.Day)
        DateTwo = New Date(DtFrom.Value.Year, DtFrom.Value.Month, DtFrom.Value.Day)

        iNumOfDays = DateDiff(DateInterval.Day, DateTwo, DateOne)

        For iLoop = 0 To iNumOfDays
            arrDate.Add(DateOne.AddDays(iLoop))
        Next

        For iLoop = 0 To arrDate.Count - 1
            Me.txtDays.Text = arrDate.Count

        Next
    End Sub

    Sub FillGrid()
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim cmd As New SqlCommand("select Sno,Name,Position,DateFrom,DateTo,NoOfDays from LeavePlan order by DateFrom", cnn)
            Dim Reader As SqlDataReader

            Me.DataGridView1.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.DataGridView1.Rows.Add(New String() {Reader.Item("Sno"), Reader.Item("Name"), Reader.Item("Position"), Reader.Item("DateFrom"), Reader.Item("DateTo"), Reader.Item("NoOfDays")})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

    End Sub

    Sub FillSchedulerControl()
        Try

            Dim FromDate As String = "01/01/" & dtpYear.Value.Year & " 00:00:00"
            Dim ToDate As String = "02/01/" & dtpYear.Value.AddYears(1).Year & " 23:59:59"
            Dim ds As New DataSet
            cnn.Open()
            Dim dap As New SqlDataAdapter("SELECT * FROM LeavePlan Where DateFrom  >= @FromDate and DateTo <= @ToDate ", cnn)
            dap.SelectCommand.Parameters.AddWithValue("@FromDate", FromDate)
            dap.SelectCommand.Parameters.AddWithValue("@ToDate", ToDate)
            dap.Fill(ds, "LeavePlan")

       

            'Me.SchedulerStorage1.Appointments.Mappings.AllDay = "AllDay"
            'Me.SchedulerStorage1.Appointments.Mappings.Description = "Name"

            'Me.SchedulerStorage1.Appointments.Mappings.Label = "Name"
            'Me.SchedulerStorage1.Appointments.Mappings.Location = "Name"
            'Me.SchedulerStorage1.Appointments.Mappings.RecurrenceInfo = "Name"
            'Me.SchedulerStorage1.Appointments.Mappings.ReminderInfo = "Name"

            'Me.SchedulerStorage1.Appointments.Mappings.Status = "Name"

            'Me.SchedulerStorage1.Appointments.Mappings.Type = "Name"

    

            Dim cmdBuilder As New SqlCommandBuilder(dap)
            dap.InsertCommand = cmdBuilder.GetInsertCommand()
            dap.DeleteCommand = cmdBuilder.GetDeleteCommand()
            dap.UpdateCommand = cmdBuilder.GetUpdateCommand()

            cnn.Close()
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.txtBranch.Text = ""
        Me.txtPosition.Text = ""
        Me.txtEmpNo.Text = ""
        Me.TxtName.Text = ""
        Me.txtDays.Text = 0
        Me.DTFrom.Value = Now
        Me.DTTo.Value = Now
    End Sub

    Private Sub DTTo_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTTo.ValueChanged
        GetNoOfDays()
    End Sub

    Private Sub DTFrom_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTFrom.ValueChanged
        GetNoOfDays()
    End Sub

    Sub FillEmpData()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select FullNameEn, Position, Branch From StaffProfiles where EmpNo= N'" & Me.txtEmpNo.Text & "'", cnn)
            Dim Reader As SqlDataReader

            Me.TxtName.Clear()
            Me.txtPosition.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.TxtName.Text = Reader.Item("FullNameEn")
                Me.txtPosition.Text = Reader.Item("Position")
                Me.txtBranch.Text = Reader.Item("Branch")

            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd1 As New SqlCommand("Select count(*) from LeavePlan where EmpNo=N'" & Me.txtEmpNo.Text & "'", cnn)

            cnn.Open()
            If cmd1.ExecuteScalar > 0 Then

                Dim cmd2 As New SqlCommand("delete from LeavePlan where EmpNo=N'" & Me.txtEmpNo.Text & "'", cnn)
                cmd2.ExecuteNonQuery()
            End If
            cnn.Close()

            If Me.TxtName.Text = "" Then
                MsgBox("Please enter a valid employee no.")
            Else

                Dim cmd As New SqlCommand("insert into LeavePlan values (N'" & Me.txtEmpNo.Text & "',N'" & Me.TxtName.Text & _
                                          "',N'" & Me.txtPosition.Text & "'," & "N'" & Me.txtBranch.Text.Trim & "'," & "N'" & Me.DTFrom.Value.ToShortDateString & _
                                          "',N'" & Me.DTTo.Value.ToShortDateString & "'," & Me.txtDays.Text & ")", cnn)
                cnn.Open()
                cmd.ExecuteNonQuery()
                cnn.Close()
                FillGrid()
                Clear()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
                MsgBox(ex.ToString)
            End If
        Finally
            Me.Cursor = Cursors.Default
        End Try

    End Sub

    Private Sub txtEmpNo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtEmpNo.TextChanged
        Me.TxtName.Text = ""
        Me.txtPosition.Text = ""
    End Sub

    Private Sub txtEmpNo_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtEmpNo.KeyUp
        If e.KeyCode = Keys.Enter Then
            FillEmpData()
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim a As New frmSearchEmp
        a.ShowDialog()

        If SelEmpID <> 0 Then
            Me.txtEmpNo.Text = SelEmpID
            FillEmpData()
        End If
    End Sub

    Private Sub frmVacationMngmnt_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillGrid()
        FillSchedulerControl()
        Me.WindowState = FormWindowState.Maximized
    End Sub

    Private Sub dtpYear_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpYear.ValueChanged
        FillSchedulerControl()
    End Sub
End Class